package com.p2l.letterblocks;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "LetterBlocks";
	private static final int DATABASE_VERSION = 1;
	
	private static final String CREATE_TABLE_CHAPTER_TRANSACTIONS = "CREATE TABLE TB_chapter_transactions (id INTEGER PRIMARY KEY AUTOINCREMENT, chapterTransactionId INTEGER NOT NULL, chapterId INTEGER NOT NULL, userId INTEGER NOT NULL, price INTEGER NOT NULL)";
	private static final String CREATE_TABLE_ITEM_TRANSACTIONS = "CREATE TABLE TB_item_transactions (id INTEGER PRIMARY KEY AUTOINCREMENT, itemId INTEGER NOT NULL, userId INTEGER NOT NULL, price INTEGER NOT NULL)";
	private static final String CREATE_TABLE_LEVELS_COMPLETED = "CREATE TABLE TB_levels_completed (id INTEGER PRIMARY KEY AUTOINCREMENT, levelCompletedId INTEGER NOT NULL, levelId INTEGER NOT NULL, userId INTEGER NOT NULL, language INTEGER NOT NULL, coin INTEGER NOT NULL)";
	private static final String CREATE_TABLE_CHAPTERS = "CREATE TABLE TB_chapters (id INTEGER PRIMARY KEY AUTOINCREMENT, chapterId INTEGER NOT NULL, name TEXT NOT NULL, price INTEGER NOT NULL, en TEXT NULL, es TEXT NULL, de TEXT NULL, dk TEXT NULL)";
	private static final String CREATE_TABLE_OBJECTS = "CREATE TABLE TB_objects (id INTEGER PRIMARY KEY AUTOINCREMENT, objectId INTEGER NOT NULL, levelId INTEGER NOT NULL, x REAL NOT NULL, y REAL NOT NULL, height REAL NOT NULL, width REAL NOT NULL, type INTEGER NOT NULL, image TEXT NULL)";
	private static final String CREATE_TABLE_LEVELS = "CREATE TABLE TB_levels (id INTEGER PRIMARY KEY AUTOINCREMENT, levelId INTEGER NOT NULL, chapterId INTEGER NOT NULL, background TEXT NOT NULL, layer TEXT NOT NULL, floor TEXT NOT NULL, dk TEXT NULL, en TEXT NULL, de TEXT NULL, es TEXT NULL)";
	private static final String CREATE_TABLE_LOGINS = "CREATE TABLE TB_logins (id INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER NOT NULL, date TEXT NOT NULL, version INTEGER NULL)";
	private static final String CREATE_TABLE_USERS = "CREATE TABLE TB_users (id INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT NULL)";
	private static final String CREATE_TABLE_ITEMS = "CREATE TABLE TB_items (id INTEGER PRIMARY KEY AUTOINCREMENT, itemId INTEGER NOT NULL, name TEXT NOT NULL, type INTEGER NOT NULL, price INTEGER NOT NULL)";
	
	public static final String TABLE_NAME_CHAPTER_TRANSACTIONS = "TB_chapter_transactions";
	public static final String TABLE_NAME_ITEM_TRANSACTIONS = "TB_item_transactions";
	public static final String TABLE_NAME_LEVELS_COMPLETED = "TB_levels_completed";
	public static final String TABLE_NAME_CHAPTERS = "TB_chapters";
	public static final String TABLE_NAME_OBJECTS = "TB_objects";
	public static final String TABLE_NAME_LEVELS = "TB_levels";
	public static final String TABLE_NAME_LOGINS = "TB_logins";
	public static final String TABLE_NAME_USERS = "TB_users";
	public static final String TABLE_NAME_ITEMS = "TB_items";
	
	public static final String[] COLUMS_CHAPTER_TRANSACTIONS = { "chapterTransactionId", "chapterId", "userId", "price" };
	public static final String[] COLUMS_ITEM_TRANSACTIONS = { "itemId", "userId" , "price" };
	public static final String[] COLUMS_LEVELS_COMPLETED = { "levelCompletedId", "levelId", "userId", "language", "coin" };
	public static final String[] COLUMS_CHAPTERS = { "chapterId", "name", "price", "en", "es", "de", "dk" };
	public static final String[] COLUMS_OBJECTS = { "objectId", "levelId", "x", "y", "height", "width", "type", "image" };
	public static final String[] COLUMS_LEVELS = { "levelId",  "chapterId", "background", "layer", "floor", "dk", "en", "de", "es" };
	public static final String[] COLUMS_LOGINS = { "userId", "date", "version" };
	public static final String[] COLUMS_USERS = { "userId", "username", "password", "email" };
	public static final String[] COLUMS_ITEMS = { "itemId", "name", "type", "price" };
	
	public Database(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_CHAPTER_TRANSACTIONS);
		db.execSQL(CREATE_TABLE_ITEM_TRANSACTIONS);
		db.execSQL(CREATE_TABLE_LEVELS_COMPLETED);
		db.execSQL(CREATE_TABLE_CHAPTERS);
		db.execSQL(CREATE_TABLE_OBJECTS);
		db.execSQL(CREATE_TABLE_LEVELS);
		db.execSQL(CREATE_TABLE_LOGINS);
		db.execSQL(CREATE_TABLE_USERS);
		db.execSQL(CREATE_TABLE_ITEMS);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CHAPTER_TRANSACTIONS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ITEM_TRANSACTIONS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_LEVELS_COMPLETED);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CHAPTERS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_OBJECTS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_LEVELS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_LOGINS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_USERS);
		db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ITEMS);
	}
	
	public long insert(SQLiteDatabase db, String table, String[] colums, String[] values) {
		ContentValues newRow = new ContentValues();
		for (int i = 0; i < colums.length; i++)
			newRow.put(colums[i], values[i]);
		long result = db.insert(table, null, newRow);
		return result;
	}
	
	public Cursor select(SQLiteDatabase db, String select, String table, String join, String where) {
		String query = select + " FROM " + table + join + where;
		Cursor cursor = db.rawQuery(query, null);
		return cursor;
	}
	
	public int update(SQLiteDatabase db, String table, String[] colums, String[] values, String where) {
		ContentValues updateRow = new ContentValues();
		for (int i = 0; i < colums.length; i++)
			updateRow.put(colums[i], values[i]);
		int result = db.update(table, updateRow, where, null);
		return result;
	}
	
	public int delete(SQLiteDatabase db, String table, String where) {
		int result = db.delete(table, where, null);
		return result;
	}
	
	public ArrayList<Item> getItems(Cursor cursor) {
		ArrayList<Item> items = new ArrayList<Item>();
		
		if (cursor.moveToFirst()) {
			do {
				items.add(new Item(new String[4], cursor.getString(1), cursor.getInt(3), cursor.getInt(0), cursor.getInt(2)));
			} while (cursor.moveToNext());
		}
		return items;
	}
	
	public ArrayList<ChapterTransaction> getChapterTransactions(Cursor cursor) {
		ArrayList<ChapterTransaction> chapter_transactions = new ArrayList<ChapterTransaction>();
		
		if (cursor.moveToFirst()) {
			do {
				chapter_transactions.add(new ChapterTransaction(cursor.getInt(1), cursor.getInt(2), cursor.getInt(3), cursor.getInt(4)));
			} while (cursor.moveToNext());
		}
		return chapter_transactions;
	}
	
	public ArrayList<Chapter> getChapters(Cursor cursor) {
		ArrayList<Chapter> chapters = new ArrayList<Chapter>();
		
		if (cursor.moveToFirst()) {
			do {
				chapters.add(new Chapter(cursor.getInt(0), cursor.getString(1), cursor.getInt(2), new String[]{ cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6) }));
			} while (cursor.moveToNext());
		}
		return chapters;
	}
	
	public ArrayList<Level> getLevels(Cursor cursor) {
		ArrayList<Level> levels = new ArrayList<Level>();
		
		if (cursor.moveToFirst()) {
			do {
				levels.add(new Level(cursor.getInt(1), cursor.getInt(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9)));
			} while (cursor.moveToNext());
		}
		return levels;
	}
	
	public ArrayList<P2LObject> getObjects(Cursor cursor) {
		ArrayList<P2LObject> objects = new ArrayList<P2LObject>();
		
		if (cursor.moveToFirst()) {
			do {
				objects.add(new P2LObject(cursor.getInt(1), cursor.getInt(2), XmlParser.getRight(cursor.getFloat(3)), XmlParser.getBottom(cursor.getFloat(4)), cursor.getFloat(5), cursor.getFloat(6), cursor.getInt(7), cursor.getString(8)));
			} while (cursor.moveToNext());
		}
		return objects;
	}
	
	public ArrayList<LevelCompleted> getLevelsCompleted(Cursor cursor) {
		ArrayList<LevelCompleted> levels_completed = new ArrayList<LevelCompleted>();
		
		if (cursor.moveToFirst()) {
			do {
				LevelCompleted l = new LevelCompleted(cursor.getInt(0), cursor.getInt(2), cursor.getInt(3), cursor.getInt(4), cursor.getInt(5) == 1);
				l.setLevelCompletedId(cursor.getInt(1));
				levels_completed.add(l);
			} while (cursor.moveToNext());
		}
		return levels_completed;
	}
	
	public ArrayList<Login> getLogins(Cursor cursor) {
		ArrayList<Login> logins = new ArrayList<Login>();
		
		if (cursor.moveToFirst()) {
			do {
				Login login = new Login(cursor.getString(2), cursor.getInt(1));
				login.setId(cursor.getInt(0));
				logins.add(login);
			} while (cursor.moveToNext());
		}
		return logins;
	}
}